Foreign Keys and Referential Integrity in MySQL

Course: MySQL Training

Topic: Foreign Keys and Referential Integrity

Course Link: JustAcademy MySQL Training Course

Demo Link: Register for a Course Demo at JustAcademy

1. Introduction to Foreign Keys and Referential Integrity

In relational databases, information is commonly distributed across multiple related tables. For example, an online shopping database may store customer information in a customers table and their orders in an orders table. A foreign key connects these tables and helps ensure that an order refers to a valid customer.

A foreign key is a column or group of columns in one table that references a primary key or an eligible unique key in another table. It establishes a relationship between the tables and allows MySQL to enforce rules about related records.

Referential integrity is the principle that relationships between tables must remain valid. For example, an order should not reference a customer that does not exist.

MySQL supports foreign key constraints through storage engines that enforce them, particularly InnoDB. Foreign keys are important for designing reliable databases and preventing invalid relationships.

1.1 Learning Objectives

• Understand foreign keys and referential integrity.
• Differentiate primary keys, unique keys, and foreign keys.
• Create foreign key constraints using SQL.
• Understand parent and child table relationships.
• Use ON DELETE and ON UPDATE actions.
• Identify and fix foreign key constraint errors.
• Manage foreign keys in one-to-one, one-to-many, and many-to-many relationships.
• Apply foreign key rules to real-world database projects.

2. Understanding Primary Keys and Foreign Keys

2.1 What Is a Primary Key?

A primary key uniquely identifies each record in a table. It cannot contain duplicate values or NULL values.

Example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

Here, customer_id uniquely identifies every customer.

2.2 What Is a Foreign Key?

A foreign key references a key in another table, or in the same table for a self-referencing relationship. The table containing the foreign key is called the child table, while the referenced table is called the parent table.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The customer_id column in orders references the customer_id column in customers. If a non-NULL customer ID is inserted into an order, that ID must match an existing referenced customer.

2.3 Primary Key vs Foreign Key

3. Understanding Parent and Child Tables

Foreign key relationships are based on parent and child tables.

• Parent table: Contains the referenced key.
• Child table: Contains the foreign key that references the parent.

Consider this relationship:

customers
---------
customer_id (Primary Key)
customer_name

orders
------
order_id (Primary Key)
order_date
customer_id (Foreign Key)

A customer can place multiple orders, but each order refers to one customer in this design. The customer is the parent record, and the order is the child record.

3.1 Example Data

Customers table:

Orders table:

Orders 101 and 103 belong to customer 1, Rahul. Order 102 belongs to customer 2, Priya. These relationships are valid because all referenced customer IDs exist in the parent table.

4. Creating Foreign Keys in MySQL

Foreign keys can be defined when a table is created or added to an existing table with ALTER TABLE.

4.1 Create the Parent Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

This table stores department details. Each department is identified by a unique department_id.

4.2 Create the Child Table with a Foreign Key

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_employees_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
) ENGINE = InnoDB;

The employees table contains a foreign key that references the departments table.

4.3 Insert Valid Records

INSERT INTO departments
    (department_id, department_name)
VALUES
    (10, 'IT'),
    (20, 'Human Resources'),
    (30, 'Finance');

INSERT INTO employees
    (employee_id, employee_name, department_id)
VALUES
    (1, 'Aarav', 10),
    (2, 'Meera', 20),
    (3, 'Kabir', 10);

These statements succeed because every non-NULL department ID in the employee records exists in the departments table.

4.4 Insert an Invalid Foreign Key

INSERT INTO employees
    (employee_id, employee_name, department_id)
VALUES
    (4, 'Neha', 99);

This insert fails because department 99 does not exist in the parent table. MySQL rejects the record to preserve referential integrity.

5. Foreign Key Syntax and Naming Conventions

5.1 General Syntax

CREATE TABLE child_table (
    child_id INT PRIMARY KEY,
    parent_id INT,
    CONSTRAINT constraint_name
        FOREIGN KEY (parent_id)
        REFERENCES parent_table(parent_id)
);

Important components:

• child_table: The table containing the foreign key.
• parent_id: The child column that stores the referenced value.
• CONSTRAINT constraint_name: An optional, descriptive name for the constraint.
• FOREIGN KEY: Declares the foreign key column or columns.
• REFERENCES: Identifies the parent table and referenced key.

5.2 Why Name Constraints?

Meaningful constraint names make database maintenance and troubleshooting easier.

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)

The prefix fk_ indicates that the object is a foreign key. The remaining name identifies the relationship. Constraint names must follow MySQL's naming rules and be unique where required, including within the database for foreign key symbols.

6. Adding a Foreign Key to an Existing Table

If a table already exists without a foreign key, you can add the constraint using ALTER TABLE.

6.1 Create Tables Without the Constraint

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    supplier_id INT
) ENGINE = InnoDB;

6.2 Add the Constraint

ALTER TABLE products
ADD CONSTRAINT fk_products_supplier
FOREIGN KEY (supplier_id)
REFERENCES suppliers(supplier_id);

MySQL validates existing data when adding the foreign key. If existing non-NULL supplier IDs do not match parent records, the operation can fail until the inconsistent data is corrected.

6.3 Add a Foreign Key with a Delete Rule

ALTER TABLE products
ADD CONSTRAINT fk_products_supplier
FOREIGN KEY (supplier_id)
REFERENCES suppliers(supplier_id)
ON DELETE SET NULL
ON UPDATE CASCADE;

This example requires supplier_id to allow NULL. If a referenced supplier is deleted, the related products' supplier IDs become NULL. If a supplier ID changes, the change is propagated to related products.

Do not run both examples unchanged against the same table: the constraint name would already exist after the first successful addition. Use the second example instead of the first when you need those actions.

7. Referential Integrity Explained

Referential integrity ensures that relationships between tables remain consistent. A foreign key prevents child records from referencing parent keys that are not present, subject to the configured constraint rules.

7.1 Core Rules

1. A non-NULL foreign key value must match a referenced key value in the parent table.
2. A parent key referenced by child records cannot normally be deleted while those child records remain, unless a permitted referential action handles the deletion.
3. Changing a referenced parent key may be restricted or propagated, depending on the foreign key's update action.
4. A foreign key column can contain NULL when its definition allows NULL. In that case, the row does not identify a parent record through that foreign key.
5. The referenced and referencing columns must have compatible definitions. For example, integer types and relevant signedness must be compatible.

7.2 Why Referential Integrity Matters

• Prevents orphan records that reference nonexistent parents.
• Protects relationships when records are inserted, updated, or deleted.
• Reduces the need to enforce every relationship rule in application code.
• Improves database consistency across multiple applications using the same database.
• Makes data models easier to understand and maintain.

8. ON DELETE Actions

The ON DELETE clause determines what happens to related child records when a referenced parent record is deleted.

8.1 RESTRICT Example

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    project_id INT,
    CONSTRAINT fk_tasks_project
        FOREIGN KEY (project_id)
        REFERENCES projects(project_id)
        ON DELETE RESTRICT
) ENGINE = InnoDB;

If tasks reference a project, MySQL prevents deleting that project until the referencing tasks have been reassigned or deleted.

8.2 CASCADE Example

CREATE TABLE blog_posts (
    post_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE post_comments (
    comment_id INT PRIMARY KEY,
    comment_text VARCHAR(500) NOT NULL,
    post_id INT NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES blog_posts(post_id)
        ON DELETE CASCADE
) ENGINE = InnoDB;

If a blog post is deleted, its related comments are automatically deleted. Use this rule only when deleting the parent should also remove its dependent child records.

8.3 SET NULL Example

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NULL,
    CONSTRAINT fk_articles_author
        FOREIGN KEY (author_id)
        REFERENCES authors(author_id)
        ON DELETE SET NULL
) ENGINE = InnoDB;

If an author is deleted, the article remains but its author_id becomes NULL. The foreign key column must permit NULL values for this action to work.

9. ON UPDATE Actions

The ON UPDATE clause determines what happens when the referenced parent key is changed.

9.1 ON UPDATE CASCADE Example

CREATE TABLE regions (
    region_code INT PRIMARY KEY,
    region_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE branches (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(100) NOT NULL,
    region_code INT,
    CONSTRAINT fk_branches_region
        FOREIGN KEY (region_code)
        REFERENCES regions(region_code)
        ON UPDATE CASCADE
) ENGINE = InnoDB;

INSERT INTO regions
VALUES (1, 'West');

INSERT INTO branches
VALUES (101, 'Mumbai Branch', 1);

UPDATE regions
SET region_code = 5
WHERE region_code = 1;

SELECT * FROM branches;

After the update succeeds, the branch's region_code is also changed to 5. In practice, primary keys are usually stable identifiers, so updating them is often unnecessary.

10. Composite Foreign Keys

A composite foreign key uses two or more columns together to reference a composite key in another table. The number, order, and compatible data types of the referencing columns must correspond to the referenced columns.

10.1 Create a Parent Table with a Composite Key

CREATE TABLE course_offerings (
    course_id INT,
    semester VARCHAR(20),
    instructor_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (course_id, semester)
) ENGINE = InnoDB;

10.2 Reference the Composite Key

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course_id INT,
    semester VARCHAR(20),
    CONSTRAINT fk_enrollment_offering
        FOREIGN KEY (course_id, semester)
        REFERENCES course_offerings(course_id, semester)
) ENGINE = InnoDB;

An enrollment must reference a course and semester combination that exists in course_offerings. A course ID that exists only in another semester is not enough to satisfy the complete foreign key.

10.3 Example Data

INSERT INTO course_offerings
    (course_id, semester, instructor_name)
VALUES
    (101, 'Spring', 'Anita'),
    (101, 'Fall', 'Vikram');

INSERT INTO enrollments
    (enrollment_id, student_name, course_id, semester)
VALUES
    (1, 'Riya', 101, 'Spring');

The enrollment is valid because the pair (101, 'Spring') exists in the parent table.

11. Foreign Keys and NULL Values

A nullable foreign key can be NULL without referencing a parent row. This is useful when a relationship is optional.

CREATE TABLE support_tickets (
    ticket_id INT PRIMARY KEY,
    ticket_title VARCHAR(200) NOT NULL,
    assigned_employee_id INT NULL,
    CONSTRAINT fk_ticket_employee
        FOREIGN KEY (assigned_employee_id)
        REFERENCES employees(employee_id)
) ENGINE = InnoDB;

A ticket can exist without an assigned employee. When an employee ID is provided, it must match a valid employee record.

Important distinctions:

• A foreign key does not automatically require a value.
• Use NOT NULL when every child record must have a parent.
• Use a nullable column when the relationship is optional.
• For a composite foreign key, MySQL's handling of NULL components follows composite foreign key rules; do not assume every component must independently match a parent when one or more components are NULL.

12. One-to-One, One-to-Many, and Many-to-Many Relationships

12.1 One-to-One Relationship

A one-to-one relationship connects one parent record to at most one child record. A foreign key combined with a UNIQUE constraint can enforce this pattern.

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    bio VARCHAR(500),
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
) ENGINE = InnoDB;

Here, each user can have at most one profile because user_id is unique in user_profiles. This example assumes a compatible users table with a key named user_id already exists.

12.2 One-to-Many Relationship

A one-to-many relationship allows one parent to have multiple child records. It is commonly implemented by placing a foreign key in the child table.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_total DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
) ENGINE = InnoDB;

One customer can have multiple orders, while every order must refer to an existing customer.

12.3 Many-to-Many Relationship

A many-to-many relationship is usually implemented through a junction table containing foreign keys to both parent tables.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_sc_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    CONSTRAINT fk_sc_course
        FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
) ENGINE = InnoDB;

The junction table ensures that every enrollment references an existing student and an existing course. Its composite primary key prevents the same student-course combination from being inserted more than once.

13. Self-Referencing Foreign Keys

A self-referencing foreign key connects records within the same table. A common example is an employee table where each employee can report to another employee.

CREATE TABLE organization_employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT NULL,
    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES organization_employees(employee_id)
        ON DELETE SET NULL
) ENGINE = InnoDB;

The manager_id column references employee_id in the same table. A top-level employee can have a NULL manager ID, while other employees can reference an existing manager.

A foreign key alone does not prevent organizational cycles, such as an employee eventually becoming their own manager through a chain of updates. Additional validation may be needed for such business rules.

14. Checking Foreign Key Constraints

Inspecting foreign key definitions is useful when troubleshooting database relationships or documenting a schema.

14.1 View Table Definition

SHOW CREATE TABLE orders;

This statement displays the table definition, including its foreign key constraints and referential actions.

14.2 Inspect Information Schema

SELECT
    TABLE_NAME,
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, CONSTRAINT_NAME;

This query lists foreign key columns in the current database and identifies the referenced table and column.

14.3 Inspect Constraint Rules

SELECT
    CONSTRAINT_NAME,
    TABLE_NAME,
    REFERENCED_TABLE_NAME,
    UPDATE_RULE,
    DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();

The results help verify whether the constraints use rules such as CASCADE, SET NULL, or RESTRICT.

15. Modifying and Dropping Foreign Keys

15.1 Drop a Foreign Key

ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;

MySQL uses the foreign key constraint name in the DROP FOREIGN KEY clause. Dropping a foreign key removes that referential integrity rule but does not automatically delete the related column.

15.2 Add the Foreign Key Again

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

Before adding the constraint again, verify that existing values in orders.customer_id have valid parent records and that the column definitions are compatible.

15.3 Rename or Change a Constraint

MySQL does not provide a general-purpose clause to edit a foreign key's actions in place. A common approach is to drop the existing foreign key and add a new one with the desired definition. Plan this carefully in production because the relationship will temporarily lack that constraint between the two operations unless they are managed in a suitable migration process.

16. Foreign Key Requirements in MySQL

• Use a storage engine that supports foreign keys, typically InnoDB.
• The referenced table and referenced key must exist when the constraint is created.
• The referenced columns must be indexed appropriately; a primary key or unique key is the preferred design.
• Referencing and referenced columns must have compatible data types. Integer size and signedness must be compatible.
• For character columns, character set and collation compatibility must be considered.
• Foreign key columns and referenced columns must be ordered consistently for composite keys.
• Use explicit constraint names to make maintenance easier.
• Ensure existing data is valid before adding a foreign key.

For portable and predictable database design, reference a primary key or a UNIQUE key rather than relying on non-unique referenced indexes. MySQL's precise foreign key capabilities can depend on its version and storage engine.

17. Common Foreign Key Errors and Troubleshooting

17.1 Find Orphan Records Before Adding a Foreign Key

An orphan record is a child record whose non-NULL foreign key does not match a parent record. The following query finds invalid order references:

SELECT o.*
FROM orders AS o
LEFT JOIN customers AS c
    ON o.customer_id = c.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.customer_id IS NULL;

If this query returns rows, review them before creating the constraint. Depending on the business rules, you may need to correct the customer ID, insert a legitimate parent record, or remove invalid child data.

17.2 Verify Column Definitions

SHOW CREATE TABLE customers;
SHOW CREATE TABLE orders;

Compare the referenced key and child column definitions, including data types, signedness, indexes, and storage engines. Also verify that the constraint references the intended parent table and key.

18. Foreign Keys and Transactions

Transactions help group related database changes into a single logical operation. They are useful when inserting or updating parent and child records together.

START TRANSACTION;

INSERT INTO customers (customer_id, customer_name)
VALUES (501, 'Ishita');

INSERT INTO orders (order_id, customer_id, order_total)
VALUES (9001, 501, 2499.00);

COMMIT;

Both inserts succeed if the statements satisfy all constraints. If an operation fails, the application can handle the error and roll back the transaction when appropriate.

START TRANSACTION;

INSERT INTO orders (order_id, customer_id, order_total)
VALUES (9002, 9999, 1200.00);

ROLLBACK;

The second example fails if customer 9999 does not exist. A rollback can undo other uncommitted changes in the transaction, but it does not make an invalid foreign key value acceptable. Handle SQL errors appropriately in the application or database client.

19. Foreign Keys and Indexes

Indexes help MySQL locate matching parent and child records efficiently. MySQL requires appropriate indexes for foreign key enforcement and can automatically create an index on the child columns when necessary.

19.1 Example of a Child Index

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

This index can also improve queries that filter orders by customer ID. Before adding an index, inspect the existing indexes because MySQL may already have created one to support the foreign key.

19.2 Performance Considerations

• Index frequently used foreign key columns when appropriate for query performance.
• Use matching data types to avoid unnecessary complexity.
• Review the cost of cascaded updates or deletes on large sets of related records.
• Avoid creating redundant indexes that duplicate existing index prefixes.
• Use EXPLAIN to investigate query plans when relationship queries are slow.

20. Foreign Keys vs Application-Level Validation

Application validation and database constraints complement each other. An application can check whether a customer exists before submitting an order, but the foreign key remains important because the database must also protect against concurrent changes and writes from other applications.

21. Best Practices for Foreign Keys and Referential Integrity

1. Use meaningful constraint names: Names such as fk_orders_customer make errors and schema changes easier to understand.
2. Reference stable identifiers: Prefer primary keys or suitable unique keys rather than mutable descriptive values.
3. Choose delete actions carefully: Use CASCADE only when child records should genuinely be deleted with the parent.
4. Use NULL deliberately: Make foreign key columns nullable only when an absent relationship is valid in the business model.
5. Create parent tables first: Define the referenced keys before creating dependent tables.
6. Validate existing records: Check for orphan records before adding constraints to an existing database.
7. Use compatible column definitions: Confirm data types, signedness, character settings, and key indexes.
8. Keep constraints enabled: Avoid bypassing integrity checks as a routine data-loading strategy.
9. Test migrations: Verify constraint creation, data updates, and deletion behavior in a development or staging environment.
10. Back up important data: Cascading operations can affect many records, so protect critical information before large changes.
11. Document relationships: Record parent-child dependencies and the reason for each referential action.
12. Use transactions appropriately: Group related operations when multiple table changes must succeed together.

22. Practical Project: Customer and Order Management

Project Goal: Create a small database that maintains valid relationships between customers and orders.

22.1 Create the Database Tables

CREATE DATABASE retail_demo;
USE retail_demo;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
) ENGINE = InnoDB;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    order_total DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE = InnoDB;

22.2 Insert Sample Customers

INSERT INTO customers
    (customer_id, customer_name, email)
VALUES
    (1, 'Rahul Sharma', '[email protected]'),
    (2, 'Priya Mehta', '[email protected]'),
    (3, 'Amit Verma', '[email protected]');

22.3 Insert Sample Orders

INSERT INTO orders
    (order_id, order_date, customer_id, order_total)
VALUES
    (101, '2026-01-10', 1, 1500.00),
    (102, '2026-01-12', 2, 2200.00),
    (103, '2026-01-15', 1, 800.00);

22.4 Display Orders with Customer Names

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    o.order_total
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
ORDER BY o.order_id;

The query joins the tables through their foreign key relationship and displays each order alongside its customer name.

22.5 Test Referential Integrity

-- Expected to fail if customer 999 does not exist
INSERT INTO orders
    (order_id, order_date, customer_id, order_total)
VALUES
    (104, '2026-01-20', 999, 1200.00);

-- Expected to fail while customer 1 has related orders
DELETE FROM customers
WHERE customer_id = 1;

The first statement violates the foreign key because customer 999 is absent. The second violates the configured ON DELETE RESTRICT rule because customer 1 has related orders.

22.6 Project Tasks

• Add a products table and an order_items table with appropriate foreign keys.
• Decide whether deleting a customer should be restricted or whether related data should be handled through another business process.
• Write a query to identify customers who have never placed an order.
• Test inserting an order with a NULL customer ID and explain why it fails in this schema.
• Inspect all foreign key constraints through INFORMATION_SCHEMA.
• Document each foreign key and explain the reason for its delete and update rules.

23. Practice Exercises

1. Create a categories table and a products table. Add a foreign key from products to categories.
2. Insert a category and then insert a product referencing that category.
3. Try inserting a product with a category ID that does not exist. Explain the result.
4. Add a foreign key to an existing table using ALTER TABLE.
5. Create a foreign key with ON DELETE CASCADE and test the result using sample records.
6. Create a nullable foreign key using ON DELETE SET NULL.
7. Create a junction table that links students and courses.
8. Write a query that finds orphan records before adding a foreign key.
9. Inspect foreign key names and referential actions through INFORMATION_SCHEMA.
10. Explain when RESTRICT is safer than CASCADE.

24. Interview Questions and Answers

Q1. What is a foreign key in MySQL?

A foreign key is a column or set of columns that references a key in another table or the same table. It enforces valid relationships between records.

Q2. What is referential integrity?

Referential integrity ensures that relationships between tables remain valid and that non-NULL foreign key values refer to existing parent keys.

Q3. Can a table contain multiple foreign keys?

Yes. A table can have multiple foreign key constraints that reference the same or different parent tables.

Q4. Can a foreign key contain duplicate values?

Yes. Duplicate foreign key values are allowed unless another constraint, such as a UNIQUE constraint, prevents them.

Q5. Can a foreign key contain NULL?

Yes, if the column allows NULL. A NULL foreign key represents an absent relationship rather than a reference to a particular parent row.

Q6. What happens when a parent record is deleted?

The outcome depends on the foreign key's ON DELETE action. The operation may be restricted, child rows may be deleted through CASCADE, or child foreign keys may become NULL through SET NULL.

Q7. What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?

CASCADE deletes the related child records. SET NULL preserves the child records but clears the foreign key value, which requires the column to allow NULL.

Q8. What is a composite foreign key?

A composite foreign key contains multiple columns that collectively reference a composite key in the parent table.

Q9. Can a foreign key reference a column that is not the primary key?

MySQL supports references to suitable indexed keys, including unique keys. For predictable schema design and portability, referencing a primary key or UNIQUE key is recommended.

Q10. How do you add a foreign key to an existing table?

Use ALTER TABLE child_table ADD CONSTRAINT ... FOREIGN KEY (...) REFERENCES parent_table(...), after verifying that existing data satisfies the constraint.

Q11. How do you remove a foreign key?

Use ALTER TABLE table_name DROP FOREIGN KEY constraint_name;. The column remains unless it is dropped separately.

Q12. Why might MySQL reject a foreign key definition?

Possible causes include incompatible column definitions, an invalid referenced key, an unsupported storage engine, a missing parent table, or an existing data inconsistency.

Q13. What is an orphan record?

An orphan record is a child record whose non-NULL foreign key does not match an existing parent key. A properly enforced foreign key prevents such inconsistent records from being created through ordinary constrained operations.

Q14. What is the role of indexes in foreign keys?

Indexes help MySQL find referenced and referencing records efficiently. MySQL requires appropriate indexes for foreign key enforcement and may create a child-side index automatically.

Q15. Why should foreign keys be used instead of application validation alone?

Database constraints protect relationships across applications and database clients, while application validation can provide additional business logic and clearer error messages.

25. Quick Revision Checklist

• Understand the difference between primary keys and foreign keys.
• Identify the parent and child tables in a relationship.
• Know how to define a foreign key with CREATE TABLE.
• Know how to add and drop constraints using ALTER TABLE.
• Understand RESTRICT, CASCADE, and SET NULL.
• Know why NULL values and NOT NULL definitions matter.
• Understand composite and self-referencing foreign keys.
• Know how to find orphan records before adding a constraint.
• Use SHOW CREATE TABLE and INFORMATION_SCHEMA to inspect constraints.
• Test constraint behavior before applying schema changes to production.

26. Conclusion

Foreign keys and referential integrity are essential components of reliable relational database design in MySQL. Foreign keys connect related tables, while referential integrity prevents invalid relationships and protects data consistency. By choosing suitable keys, compatible column definitions, and appropriate ON DELETE and ON UPDATE actions, developers can build databases that are easier to maintain and less vulnerable to inconsistent data.

Practice these concepts by creating parent and child tables, testing valid and invalid inserts, checking deletion behavior, and inspecting constraints through MySQL's metadata tables.

Learn More: Visit JustAcademy MySQL Training to explore MySQL learning opportunities, or register for a course demo.